/*
DATA POST VENTA
STALIN MORA BURGOS 19/07/2012
*/

select
vl.lot_pro as idproyecto,
vl.lot_urb AS idurbanizacion,
vl.lot_prom AS idempresa, 
vl.lot_ctg as Categoria,
vl.lot_sta as EstadoLote,
vl.lot_sta_pro as EstadoLotePro,
vl.lot_mz AS Mz,
vl.lot_num AS Lote,
pm.descripcion AS Modelo,
vc.con_codigo AS Contrato,
vc.fec_entrega_real AS Fec_entrega,
ConstructorId = (select top 1 cn.emp_id from medusa.samedusa.tb_med_lxc lxc 
				INNER JOIN medusa.samedusa.tb_med_cnt cn  
					ON lxc.cnt_id = cn.cnt_id
				where vl.lot_id=lxc.lot_id
				and lxc.cnt_id=cn.cnt_id
				and cn.tipo_cnt='0'
				and cn.cnt_principal= 'S'
				and cn.cnt_st <> 'X'
				ORDER BY cn.cnt_fec DESC),
EquipadorId = (select top 1 cn.emp_id from medusa.samedusa.tb_med_lxc lxc 
				INNER JOIN medusa.samedusa.tb_med_cnt cn  
					ON lxc.cnt_id = cn.cnt_id
				where vl.lot_id=lxc.lot_id
				and lxc.cnt_id=cn.cnt_id
				and cn.tipo_cnt='2'
				and cn.cnt_principal= 'S'
				and cn.cnt_st <> 'X'
				ORDER BY cn.cnt_fec DESC),
vc.con_tip_clie as id_tipo_cliente,				
vc.cli_id AS id_cliente,
ISNULL(CASE
		WHEN (vc.con_tip_clie = '00') THEN dp.dap_num_real
		WHEN (vc.con_tip_clie = '01') THEN dp.dap_num_real
		WHEN (vc.con_tip_clie = '02') THEN de.dae_ruc_real
		END,'') as Identificacion,
ISNULL(CASE
		WHEN (vc.con_tip_clie = '00') THEN dp.dap_nom1
		WHEN (vc.con_tip_clie = '01') THEN dp.dap_nom1
		WHEN (vc.con_tip_clie = '02') THEN de.dae_raz
		END,'') as Nombre,
ISNULL(CASE
		WHEN (vc.con_tip_clie = '00') THEN dp.dap_ape1
		WHEN (vc.con_tip_clie = '01') THEN dp.dap_ape2
		WHEN (vc.con_tip_clie = '02') THEN ''
		END,'') as Apellido,
ISNULL((SELECT TOP 1 dd.dir_desc_dir1 FROM data.monica.tb_data_dir dd
				WHERE vc.cli_id = dd.icd_id
				and vc.con_tip_clie = dd.tid_id
				and dd.dir_princ_clie = 'S'
		),'') as Direccion,
ISNULL((SELECT TOP 1 dd1.dir_desc_dir1 FROM data.monica.tb_data_dir dd1
				WHERE vc.cli_id = dd1.icd_id
				and dd1.dir_tipo_dir = 6
	  ),'') AS Correo,
ISNULL((SELECT TOP 1 dd.dir_nvubi1_clie FROM data.monica.tb_data_dir dd
				WHERE vc.cli_id = dd.icd_id
				and vc.con_tip_clie = dd.tid_id
				and dd.dir_princ_clie = 'S'
		),'') as Telefono,
ISNULL((SELECT TOP 1 dd.dir_nvubi2_clie FROM data.monica.tb_data_dir dd
				WHERE vc.cli_id = dd.icd_id
				and vc.con_tip_clie = dd.tid_id
				and dd.dir_princ_clie = 'S'
		),'') as Celular,
vc.con_oficial as Oficial_venta,
ISNULL(oc.nombre,vc.ofic_cred) as Oficial_credito
INTO #POSTVENTA
from ventas.saventa.tb_ven_lot vl
INNER JOIN ventas.saventa.tb_ven_con vc
	ON vl.lot_id = vc.lot_id
	and vc.con_sta = 'A'
INNER JOIN ventas.saventa.tb_ven_proy_mod pm
	ON vc.mod_id = pm.mod_id_vtas
LEFT JOIN data.monica.tb_data_dap dp
	ON vc.cli_id = dp.icd_id
	and vc.con_tip_clie=dp.tid_icd
	and dp.dap_status_clie = 'A'
LEFT JOIN data.monica.tb_data_dae de
	ON vc.cli_id = de.icd_id
	and vc.con_tip_clie = de.tid_id
LEFT JOIN ventas.saventa.tb_ven_ofic_cred oc
	on vc.ofic_cred = oc.ofic_cred
	and vl.lot_urb = oc.urb_id
where 
vl.lot_urb IN('00016','00006','00017','00005')
and vl.lot_sta_pro='00005'
and vc.con_tipp NOT IN ('00005', '00006')
order by 1,5,6

SELECT 
PS.idproyecto,
PS.idurbanizacion,
PS.idempresa,
PS.Categoria,
PS.EstadoLote,
Ps.EstadoLotePro,
PS.Mz,
PS.Lote,
PS.Modelo,
PS.Contrato,
Ps.Fec_entrega,
Ps.ConstructorId,
ISNULL((SELECT dp.dap_ape1 + ' ' + dp.dap_nom1 FROM data.monica.tb_data_dap dp
		where ConstructorId=dp.icd_id)
,(SELECT de.dae_raz FROM data.monica.tb_data_dae de
		where ConstructorId=de.icd_id)) as NombreConst,
PS.EquipadorId,
ISNULL((SELECT dp.dap_ape1 + ' ' + dp.dap_nom1 FROM data.monica.tb_data_dap dp
		where EquipadorId=dp.icd_id)
,(SELECT de.dae_raz FROM data.monica.tb_data_dae de
		where EquipadorId=de.icd_id)) As NombreEqui,
PS.id_tipo_cliente,
PS.id_cliente,
PS.Identificacion,
Ps.Nombre,
PS.Apellido,
Ps.Direccion,
Ps.Correo,
ps.Telefono As Telefono1,
ps.Celular AS Telefono2,
vv.nombre AS Oficial_venta,
ps.Oficial_credito
INTO #www_post_venta_table
FROM #POSTVENTA PS
INNER JOIN ventas.saventa.tb_ven_vendedores vv
	ON PS.Oficial_venta = vv.ven_codigo
	and PS.idurbanizacion = vv.urb_id 
WHERE Modelo NOT IN ('SOLAR')


DROP TABLE #POSTVENTA  --Elimina tabla #POSTVENTA
/*Actualiza registros que hayan sufrido modificaciones y estableciendoles con estado U */
update ventas.saventa.post_venta_table 
set Direccion=wpvt.Direccion,
set  
estado_reg = 'U'
from ventas.saventa.post_venta_table pvc
INNER JOIN #www_post_venta_table wpvt
	ON pvc.id_cliente = wpvt.id_cliente
	and wpvt.id_tipo_cliente= pvc.id_tipo_cliente
	and pvc.Contrato = wpvt.Contrato
WHERE ISNULL(pvc.Direccion,space(1)) <> ISNULL(wpvt.Direccion,SPACE(1))
OR ISNULL( pvc.Apellido, SPACE(1))<> ISNULL(wpvt.Apellido,SPACE(1))
OR ISNULL( pvc.Correo, SPACE(1))<> ISNULL(wpvt.Correo,SPACE(1))
OR ISNULL( pvc.Fec_entrega, SPACE(1))<> ISNULL(wpvt.Fec_entrega,SPACE(1))
OR ISNULL( pvc.id_cliente, SPACE(1))<> ISNULL(wpvt.id_cliente,SPACE(1))
OR ISNULL( pvc.Nombre, SPACE(1))<> ISNULL(wpvt.Nombre,SPACE(1))
OR ISNULL( pvc.Oficial_venta, SPACE(1))<> ISNULL(wpvt.Oficial_venta,SPACE(1))
OR ISNULL( pvc.Oficial_credito, SPACE(1))<> ISNULL(wpvt.Oficial_credito,SPACE(1))
OR ISNULL( pvc.Telefono1, SPACE(1))<> ISNULL(wpvt.Telefono1,SPACE(1))
OR ISNULL( pvc.Telefono2, SPACE(1))<> ISNULL(wpvt.Telefono2,SPACE(1))
OR ISNULL( pvc.Identificacion, SPACE(1))<> ISNULL(wpvt.Identificacion,SPACE(1))
OR ISNULL( pvc.EstadoLote, SPACE(1))<> ISNULL(wpvt.EstadoLote,SPACE(1))
OR ISNULL( pvc.EstadoLotePro, SPACE(1))<> ISNULL(wpvt.EstadoLotePro,SPACE(1))
OR ISNULL( pvc.Apellido, SPACE(1))<> ISNULL(wpvt.Apellido,SPACE(1))

/*INSERTA REGISTROS NUEVOS EN LA TABLA POST_VENTA_TABLE*/
INSERT INTO [ventas].[saventa].[post_venta_table]
           (idproyecto
           ,idurbanizacion
           ,idempresa
           ,Categoria
           ,EstadoLote
           ,EstadoLotePro
           ,Mz
           ,Lote
           ,Modelo
           ,Contrato
           ,Fec_entrega
           ,ConstructorId
           ,NombreConst
           ,EquipadorId
           ,NombreEqui
           ,id_tipo_cliente
           ,id_cliente
           ,Identificacion
           ,Nombre
           ,Apellido
           ,Direccion
           ,Correo
           ,Telefono1
           ,Telefono2
           ,Oficial_venta
           ,Oficial_credito
           ,estado_reg)
SELECT idproyecto
      ,idurbanizacion
      ,idempresa
      ,Categoria
      ,EstadoLote
      ,EstadoLotePro
      ,Mz
      ,Lote
      ,Modelo
      ,Contrato
      ,Fec_entrega
      ,ConstructorId
      ,NombreConst
      ,EquipadorId
      ,NombreEqui
      ,id_tipo_cliente
      ,id_cliente
      ,Identificacion
      ,Nombre
      ,Apellido
      ,Direccion
      ,Correo
      ,Telefono1
      ,Telefono2
      ,Oficial_venta
      ,Oficial_credito
      ,'I' FROM #www_post_venta_table wpvt
     WHERE wpvt.Contrato NOT IN (SELECT pvt.contrato FROM ventas.saventa.post_venta_table pvt) 

DROP TABLE #www_post_venta_table

/*INSERTA REGISTROS A TABLA DE MYSQL*/
INSERT INTO OPENQUERY (POSTVENTA, 'select idproyecto, idurbanizacion, idempresa, Categoria, EstadoLote, EstadoLotePro, Mz, Lote, Modelo, Contrato, Fec_entrega, Constructorid, NombreConst, EquipadorId, NombreEqui, id_tipo_cliente,id_cliente, Identificacion, Nombre, Apellido, Direccion, Correo, Telefono1, Telefono2, Oficial_venta, Oficial_credito,estado from inmueble')
SELECT idproyecto, idurbanizacion, idempresa, Categoria, EstadoLote, EstadoLotePro, Mz, Lote, Modelo, Contrato, Fec_entrega, Constructorid, NombreConst, EquipadorId, NombreEqui, id_tipo_cliente,id_cliente, Identificacion, Nombre, Apellido, Direccion, Correo, Telefono1, Telefono2, Oficial_venta, Oficial_credito, 'I' from ventas.saventa.post_venta_table
WHERE estado_reg = 'I' 

UPDATE ventas.saventa.post_venta_table SET estado_reg='P' WHERE estado_reg='I'

--VARIABLES POST VENTA
DECLARE 
	@idproyecto VARCHAR(5),
	@idurbanizacion VARCHAR(5),
	@idempresa VARCHAR(15),
	@Categoria VARCHAR(5),
	@EstadoLote VARCHAR(5),
	@EstadoLotePro VARCHAR(5),
	@Mz VARCHAR(5),
	@Lote VARCHAR(5),
	@Modelo VARCHAR(255),
	@Contrato VARCHAR(15),
	@Fec_entrega datetime,
	@ContructorId VARCHAR(15),
	@NombreConst VARCHAR(31),
	@EquipadorId VARCHAR(15),
	@NombreEqui VARCHAR(31),
	@id_tipo_cliente VARCHAR(2),
	@id_cliente VARCHAR(15),
	@Identificacion VARCHAR(15),
	@Nombre VARCHAR(100),
	@Apellido VARCHAR(100),
	@Direccion VARCHAR(140),
	@Correo VARCHAR(140),
	@Telefono1 VARCHAR(21),
	@Telefono2 VARCHAR(21),
	@Oficial_venta VARCHAR(60),
	@Oficial_credito VARCHAR(255),
	@tmp NVARCHAR(4000),
	@estado VARCHAR(1)
	
set @estado = 'U'	
declare post_venta cursor for
	SELECT idproyecto, idurbanizacion, idempresa, Categoria, EstadoLote, EstadoLotePro, Mz, Lote, Modelo, Contrato, Fec_entrega, Constructorid, NombreConst, EquipadorId, NombreEqui, id_tipo_cliente,id_cliente, Identificacion, Nombre, Apellido, Direccion, Correo, Telefono1, Telefono2, Oficial_venta, Oficial_credito from ventas.saventa.post_venta_table
	where estado_reg = 'U'

OPEN post_venta
FETCH post_venta INTO @idproyecto,@idurbanizacion,@idempresa,@Categoria,@EstadoLote,@EstadoLotePro,@Mz,@Lote,@Modelo,@Contrato,@Fec_entrega,@ContructorId,@NombreConst,@EquipadorId,@NombreEqui,@id_tipo_cliente,@id_cliente,@Identificacion,@Nombre,@Apellido,@Direccion,@Correo,@Telefono1,@Telefono2,@Oficial_venta,@Oficial_credito
WHILE @@FETCH_STATUS = 0
BEGIN

	set @tmp = N' UPDATE OPENQUERY(POSTVENTA, '' select Direccion, estado_reg from inmueble where id_cliente= "'+
				@id_cliente+'" and id_tipo_cliente= "' +@id_tipo_cliente+'" and Contrato ="'+ @Contrato+'"'')
				set Direccion = ''' + @Direccion + ''',estado='''+@estado+''', EstadoLote='''+@EstadoLote+ ''', EstadoLotePro='''+@EstadoLotePro+''' ,
				ContructorId ='''+@ContructorId+ ''',EquipadorId='''+@EquipadorId+''',id_cliente='''+@id_cliente+''', Identificacion='''+@Identificacion+''',
				Nombre='''+@Nombre+''', Apellido='''+@Apellido+''',Correo='''+@Correo+''', Telefono1='''+@Telefono1+''', Telefono2='''+@Telefono2+''',
				Oficial_venta='''+@Oficial_venta+''',Oficial_credito='''+@Oficial_credito+''''						
				
	exec sp_executesql @tmp
	

	
	UPDATE ventas.saventa.post_venta_table SET estado_reg='P' WHERE estado_reg='U' and id_cliente = @id_cliente and id_tipo_cliente=@id_tipo_cliente and Contrato = @Contrato
	
FETCH post_venta INTO @idproyecto,@idurbanizacion,@idempresa,@Categoria,@EstadoLote,@EstadoLotePro,@Mz,@Lote,@Modelo,@Contrato,@Fec_entrega,@ContructorId,@NombreConst,@EquipadorId,@NombreEqui,@id_tipo_cliente,@id_cliente,@Identificacion,@Nombre,@Apellido,@Direccion,@Correo,@Telefono1,@Telefono2,@Oficial_venta,@Oficial_credito

END
CLOSE post_venta
deallocate post_venta	 
